#!/usr/bin/python
#coding=utf-8
#
'''
Created on Dec 9, 2012

@author: hbliu
'''

import MySQLdb
import urllib2
import json
import time
from dateutil import parser

'''
data = urllib2.urlopen('https://api.weibo.com/2/statuses/ \
                        public_timeline.json?access_token=2.005SaepB04u4Lx0caa7e88d4zDV1PD' \
                        ).read().decode('utf-8')
'''

class DataCollection:
    def __init__(self, _host='localhost', _user='root', _passwd='hbliu', _port=3306, _charset='utf8'):
        try:
            self.con = MySQLdb.connect(host=_host, user=_user, passwd=_passwd, port=_port, charset='utf8')
            self.cur = self.con.cursor()
            self.cur.execute('create database if not exists sina_weibo charset=utf8')
            self.con.select_db('sina_weibo')
            
            self.cur.execute('create table if not exists public_timeline(\
                                            id bigint not null,\
                                            created_at datetime,\
                                            source varchar(500),\
                                            favorited bool,\
                                            truncated bool,\
                                            in_reply_to_status_id varchar(50),\
                                            in_reply_to_user_id varchar(50),\
                                            in_reply_to_screen_name varchar(100),\
                                            thumbnail_pic varchar(100),\
                                            bmiddle_pic varchar(100),\
                                            original_pic varchar(100),\
                                            reposts_count int,\
                                            comments_count int,\
                                            attitudes_count int,\
                                            text varchar(2000),\
                                            user_id bigint,\
                                            primary key(id))')
            self.cur.execute('create table if not exists users( \
                                id bigint not null,\
                                screen_name varchar(100),\
                                name varchar(100),\
                                province int,\
                                city int,\
                                location varchar(200),\
                                description varchar(2000),\
                                profile_image_url varchar(200),\
                                url varchar(200),\
                                profile_url varchar(200),\
                                domain varchar(200),\
                                weihao varchar(200),\
                                gender varchar(20),\
                                followers_count bigint,\
                                friends_count bigint,\
                                statuses_count bigint,\
                                favourites_count bigint,\
                                created_at datetime,\
                                verified bool,\
                                avatar_large varchar(200),\
                                lang varchar(30),\
                                primary key(id))')
            self.con.commit()
        except MySQLdb.Error,e:
            print "MySQL Error:%d:%s" % (e.args[0], e.args[1])
            
    def __del__(self):
        self.cur.close()
        self.con.close()
   
        
#weibodb = MySqlOpt()     
    def get_data(self):  
        data = urllib2.urlopen('https://api.weibo.com/2/statuses/public_timeline.json?access_token=2.005SaepB04u4Lx0caa7e88d4zDV1PD').read().decode('utf-8')
        data_decode = json.loads(data)
        statuses = data_decode['statuses']
        for i in range(len(statuses)):           
            item = statuses[i]
            my_date = parser.parse(item['created_at'])
            my_date.strftime('%Y-%m-%d %H:%M:%S')
            
            my_id = item['id']
            created_at = my_date
            source = item['source'] 
            favorited = item['favorited']
            truncated = item['truncated']
            in_reply_to_status_id = item['in_reply_to_status_id']
            in_reply_to_user_id = item['in_reply_to_user_id']
            in_reply_to_screen_name = item['in_reply_to_screen_name']            
            thumbnail_pic = ''
            bmiddle_pic = ''
            original_pic = ''
            if 'thumbnail_pic' in item.keys():
                thumbnail_pic = item['thumbnail_pic']
            if 'bmiddle_pic' in item.keys():
                bmiddle_pic = item['bmiddle_pic']
            if 'original_pic' in item.keys():
                original_pic = item['original_pic']
                
            reposts_count = item['reposts_count']
            comments_count = item['comments_count']
            attitudes_count = item['attitudes_count']
            text = item['text']              
            user_id = item['user']['id']    
            
            data_value = [my_id,created_at,source,favorited,truncated,in_reply_to_status_id,\
                          in_reply_to_user_id,in_reply_to_screen_name,thumbnail_pic,\
                          bmiddle_pic,original_pic,reposts_count,comments_count,attitudes_count,text,user_id]
            item_user = item['user']
            screen_name = item_user['screen_name']
            name = item_user['name']
            province = item_user['province']
            city = item_user['city']
            location = item_user['location']
            description = item_user['description']
            profile_image_url = item_user['profile_image_url']
            url = item_user['url']
            profile_url = item_user['profile_url']
            domain = item_user['domain']
            weihao = item_user['weihao']
            gender = item_user['gender']
            followers_count = item_user['followers_count']
            friends_count = item_user['friends_count']
            statuses_count = item_user['statuses_count']
            favourites_count = item_user['favourites_count']
            
            user_date = parser.parse(item_user['created_at'])
            user_date.strftime('%Y-%m-%d %H:%M:%S')
            created_at = user_date
            verified = item_user['verified']
            avatar_large = item_user['avatar_large']
            lang = item_user['lang']
            user_value = [user_id,screen_name,name,province,city,location,description,profile_image_url,\
                          url,profile_url,domain,weihao,gender,followers_count,friends_count,statuses_count,\
                          favourites_count,created_at,verified,avatar_large,lang]
            try:
                print data_value
                self.cur.execute('insert into public_timeline \
                                 values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',\
                                  data_value)
                self.cur.execute('insert into users \
                                values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',\
                                user_value)
                self.con.commit()
            except MySQLdb.Error: 
                print 'MySQL Error'    

for i in range(5000):               
    DataCollection().get_data()  
    time.sleep(10)                           
                                